package com.shujia

import scala.io.Source

object Code34Top10 {
  def main(args: Array[String]): Unit = {
    val stuInfoList: List[(String, String, Int, String, String)] = Source
      .fromFile("scala_code/data/students.txt")
      .getLines()
      // 将读取到的每一行数据转换成List中的元素
      .toList
      .map(
        (line: String) => {
          val colum: Array[String] = line.split(",")
          // (id,name,age,gender,clazz)
          //  Tuple相比较Array 其元素的数据类型可以多样
          (colum(0), colum(1), colum(2).toInt, colum(3), colum(4))
        }
      )


    val stuTotalScore: Map[String, Int] = Source
      .fromFile("scala_code/data/score.txt")
      .getLines()
      // 将读取到的每一行数据转换成List中的元素
      .toList
      .map(
        line => {
          val column: Array[String] = line.split(",")
          // (id,(科目ID,成绩))
          (column(0), column(1), column(2))
        }
      )
      .groupBy(
        oneScore => oneScore._1
      )
      .map(
        keyValue => {
          val stuID: String = keyValue._1
          val oneStuScore: List[(String, String, String)] = keyValue._2

          // 求一个学生的总分： 先取出成绩，再求和
          val total_score: Int = oneStuScore
            // _ 代表一个成绩
            .map(_._3.toInt)
            // sum要求是一个int类型
            .sum

          // 返回一个学生ID和其总分
          (stuID, total_score)
        }
      )

    stuInfoList
      .map(
        oneInfo => {
          if (stuTotalScore.contains(oneInfo._1)) {
            val total: Int = stuTotalScore(oneInfo._1)
            (oneInfo._1, oneInfo._2, oneInfo._3, oneInfo._4, oneInfo._5, total)
          } else {
            (oneInfo._1, oneInfo._2, oneInfo._3, oneInfo._4, oneInfo._5, 0)
          }
        }
      )
      // 根据总分进行排序
      .sortBy(
        oneStu => -oneStu._6
      )
      // take取出结果中的前10个
      .take(10)
      .foreach(println)

    """
      |SELECT
      |T2.*
      |,T1.total_score
      |FROM(
      |SELECT
      |id
      |,sum(score) as total_score
      |FROM score
      |group by id)T1
      |join student T2 ON T1.id = T2.id
      |order by T1.total_score desc
      |limit 10
      |""".stripMargin



  }
}
